/*
 * Notifications.java
 *
 * Created on November 15, 2006, 10:31 PM
 */

package org.foment.gem.beans;

import javax.swing.*;
import java.awt.*;
import javax.swing.event.ListSelectionEvent;
import org.foment.joliage3.sql.Database;
import java.sql.*;
import org.foment.joliage3.sql.Table;
import static org.foment.joliage3.core.internationalization.Internationalization.i18n;

/**
 *
 * @author  jaksa
 */
public class Notifications extends javax.swing.JPanel implements java.io.Serializable, org.foment.joliage3.sql.Table.TableListener {
    
    /** Creates new form Notifications */
    public Notifications() {
        initComponents();
        listMessages.setCellRenderer(new MessageRenderer());
        try {
            Database.getTable("EmployeeJobType").addListener(this);
            Database.getTable("Employee").addListener(this);
            Database.getTable("JobType").addListener(this);
            Database.getTable("EmployeeJobDetails").addListener( this );
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        fetchNotifications();
        
/*
        
        listMessages.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent e)
            {
                int oldSelectedEmployeeID = _selectedEmployee;


                Message m = (Message) listMessages.getSelectedValue();
                
                if (m == null)
                    _selectedEmployee = -1;
                else
                    _selectedEmployee = m.getEmployeeID();

                propertyChangeSupport.firePropertyChange ("selectedEmployeeID", new Integer (oldSelectedEmployeeID), new Integer (_selectedEmployee));
                listMessages.clearSelection();
            }
        });

        
        */
        listMessages.addListSelectionListener(new javax.swing.event.ListSelectionListener() {
            public void valueChanged(ListSelectionEvent e) {
                int oldSelectedEmployeeID = _selectedEmployee;

                if (!e.getValueIsAdjusting()) {
                    Message m = (Message) listMessages.getSelectedValue();
                    if (m == null)
                        _selectedEmployee = -1;
                    else
                        _selectedEmployee = m.getEmployeeID();

                    propertyChangeSupport.firePropertyChange ("selectedEmployeeID", new Integer (oldSelectedEmployeeID), new Integer (_selectedEmployee));
                }
            }
        } );

    }
    
    /** Reads data from database and checks if there is need for
        creating new notifications
     */
    
    public int getSelectedEmployee()
    {
        return _selectedEmployee;
    }
    
    public void fetchNotifications()
    {
        try {
            _messages.clear();

            // if employee position has expired, display appropriate message
            
            /*
            String query = "SELECT emp.EmployeeGender, emp.EmployeeID, " +
                        "emp.EmployeeFirstName, emp.EmployeeSurname, " +
                        "jt.JobTypeTitle FROM EmployeeJobType as empjt, Employee as emp, " +
                        "JobType as jt WHERE empjt.EmployeeJobTypeExpirationDate < NOW() " +
                        "AND empjt.EmployeeID = emp.EmployeeID AND empjt.JobTypeID = " +
                        "jt.JobTypeID AND emp.EmployeeJobStatus = 'z';";
            */
            // Prvo ubacujemo one koji imaju status mirovanja.
            String query = "SELECT e.EmployeeID, e.EmployeeFirstName, e.EmployeeSurname FROM Employee e, EmployeeJobDetails ejd "
                    + "WHERE"
                    + " e.EmployeeID = ejd.EmployeeID AND ejd.EmployeeJobDetailsInactive = '1'";

            Connection  conn = Database.getConnection();
            ResultSet rs = conn.createStatement().executeQuery(query);
            rs.beforeFirst();
            while (rs.next())
            {
                String firstName = rs.getString("EmployeeFirstName");
                String lastName  = rs.getString("EmployeeSurname");
                //String gender    = rs.getString("EmployeeGender");
                int employeeID   = rs.getInt("EmployeeID");

                String message = "Zaposleni <b>$FULL_NAME</b> ima status <b>mirovanja</b>";
                message = message.replace("$FULL_NAME", lastName + ", " + firstName);
                _messages.addElement(new Message(message, employeeID));
            }
            rs.close();
            

            /*Osobe kojima istice pozicija na kojoj trenutno rade. Recimo, profesori su angazovani na 4 godine.*/

            
            query = "SELECT (TO_DAYS(DATE(emjt.EmployeeJobTypeExpirationDate)) - TO_DAYS(DATE(NOW()))) " +
                    "AS dayNum, emp.EmployeeGender, emp.EmployeeFirstName, emp.EmployeeSurname, emp.EmployeeID, " +
                    "jt.JobTypeTitle FROM Employee emp, JobType jt, EmployeeJobType emjt WHERE emp.EmployeeID = " +
                    "emjt.EmployeeID AND emjt.JobTypeID = jt.JobTypeID AND (emjt.EmployeeJobTypeExpirationDate < " +
                    "DATE_ADD(NOW(), INTERVAL "+ notificationDateRange +" DAY) OR emjt.EmployeeJobTypeExpirationDate < NOW()) AND " +
                    "emp.EmployeeJobStatus = 'z' AND emjt.EmployeeJobTypeActive = '1' ;";
            //System.out.println(query);

            query = "SELECT (TO_DAYS(DATE(emjt.EmployeeJobTypeExpirationDate)) - TO_DAYS(DATE(NOW()))) " +
                    "AS dayNum, emp.EmployeeGender, emp.EmployeeFirstName, emp.EmployeeSurname, emp.EmployeeID, " +
                    "jt.JobTypeTitle FROM Employee emp, JobType jt, EmployeeJobType emjt WHERE emp.EmployeeID = " +
                    "emjt.EmployeeID AND emjt.JobTypeID = jt.JobTypeID AND "
                    + "("
                    + "( emjt.JobTypeID IN (5,6) AND emjt.EmployeeJobTypeExpirationDate < DATE_ADD(NOW(), INTERVAL " + notificationDateRange + " DAY) ) OR "
                    + "( emjt.JobTypeID NOT IN (5,6) AND emjt.EmployeeJobTypeExpirationDate < DATE_ADD(NOW(), INTERVAL " + notificationDateRangeAssistent + " DAY) ) OR "
                    + "emjt.EmployeeJobTypeExpirationDate < NOW()"
                    + ") AND "
                    + "emp.EmployeeJobStatus = 'z' AND emjt.EmployeeJobTypeActive = '1' ;";


            
            rs = conn.createStatement().executeQuery(query);
            rs.beforeFirst();
            
            while (rs.next())
            {
                String firstName    = rs.getString("EmployeeFirstName");
                String lastName     = rs.getString("EmployeeSurname");
                String jobTypeTitle = rs.getString("JobTypeTitle");
                int employeeID      = rs.getInt("EmployeeID");
                int dayNum          = rs.getInt("dayNum");
               
                String message = new String();
                
                if (dayNum > 0)
                {
                    message = i18n("<b>$FULL_NAME</b>: position <b>$POSITION_NAME</b> will expire in <b>$DAY_NUM</b> day(s)")
                           .replace("$FULL_NAME", lastName + ", " + firstName)
                           .replace("$POSITION_NAME", jobTypeTitle)
                           .replace("$DAY_NUM", String.valueOf(dayNum));
                }
                else if (dayNum == 0)
                {
                    message = i18n("<b>$FULL_NAME</b>: position <b>$POSITION_NAME</b> expires <b>today</b>")
                           .replace("$FULL_NAME", lastName + ", " + firstName)
                           .replace("$POSITION_NAME", jobTypeTitle);
                }
                else
                {
                    message = i18n("<b>$FULL_NAME</b>: position <b>$POSITION_NAME</b> expired <b>$DAY_NUM</b> day(s) ago")
                           .replace("$FULL_NAME", lastName + ", " + firstName)
                           .replace("$POSITION_NAME", jobTypeTitle)
                           .replace("$DAY_NUM", String.valueOf(-dayNum));
                }
                
                _messages.addElement(new Message(message, employeeID));
            }
            rs.close();
            
            /* if employee has exceeded age number, or working years number
             * display message for employee retirement
             * there is a difference between male and female age limit!
             * And it is NOT important on which positions employee is working
             */
            /*
            String tmp = "SELECT EmployeeGender, EmployeeID, EmployeeFirstName, EmployeeSurname FROM Employee " +
                    "WHERE (EmployeeGender = 'm' AND EmployeeWorkingYears " +
                    ">= " + maleWorkYearsLimit + ") OR (EmployeeGender = 'm' AND " +
                    "YEAR(NOW()) - YEAR(EmployeeDateOfBirth) >= " + maleAgeLimit + ") OR " +
                    "(EmployeeGender = 'f' AND EmployeeWorkingYears >= "+ femaleWorkYearsLimit + ") " +
                    "OR (EmployeeGender = 'f' AND YEAR(NOW()) - YEAR(EmployeeDateOfBirth) >= " + femaleAgeLimit + ") AND EmployeeJobStatus = 'z';";

             *
             */
            /*Ubacujemo lica koja su stekla uslov za starosnu penziju*/
            query = "SELECT EmployeeGender, EmployeeID, EmployeeFirstName, EmployeeSurname FROM Employee " +
                    "WHERE ((EmployeeGender = 'm' AND " +
                    "YEAR(NOW()) - YEAR(EmployeeDateOfBirth) >= " + maleAgeLimit + ") " + 
                    "OR (EmployeeGender = 'f' AND YEAR(NOW()) - YEAR(EmployeeDateOfBirth) >= " + femaleAgeLimit + ")) AND EmployeeJobStatus = 'z';";

         
            
            rs = conn.createStatement().executeQuery(query);
            rs.beforeFirst();
            
            while (rs.next())
            {
                String firstName = rs.getString("EmployeeFirstName");
                String lastName  = rs.getString("EmployeeSurname");
                //String gender    = rs.getString("EmployeeGender");
                int employeeID   = rs.getInt("EmployeeID");
                
                String message = i18n("Employee <b>$FULL_NAME</b> is ready for <b>retirement</b>")
                     .replace("$FULL_NAME", lastName + ", " + firstName);
                _messages.addElement(new Message(message, employeeID));
            }
            rs.close();

            // sad ubacujemo kome istice na odredjeno...
            query = "SELECT (TO_DAYS(DATE(emp.EmployeeJobExpirationDate)) - TO_DAYS(DATE(NOW()))) " +
                    "AS dayNum, emp.EmployeeGender, emp.EmployeeFirstName, emp.EmployeeSurname, emp.EmployeeID " +
                    "FROM Employee emp WHERE  " +
                    "EmployeeJobStatus = 'z' AND " +
                    "(emp.EmployeeJobExpirationDate < " +
                    "DATE_ADD(NOW(), INTERVAL "+ notificationJobExpireRange +" DAY) OR emp.EmployeeJobExpirationDate < NOW() " +
                    ");";

            rs = conn.createStatement().executeQuery(query);
            rs.beforeFirst();

            while (rs.next())
            {
                String firstName    = rs.getString("EmployeeFirstName");
                String lastName     = rs.getString("EmployeeSurname");
                int employeeID      = rs.getInt("EmployeeID");
                int dayNum          = rs.getInt("dayNum");

                String message = new String();

                if (dayNum > 0)
                {
                    message = i18n("<b>$FULL_NAME</b>: job contract will expire in <b>$DAY_NUM</b> day(s)")
                           .replace("$FULL_NAME", lastName + ", " + firstName)
                           .replace("$DAY_NUM", String.valueOf(dayNum));
                }
                else if (dayNum == 0)
                {
                    message = i18n("<b>$FULL_NAME</b>: job contract expires <b>today</b>")
                           .replace("$FULL_NAME", lastName + ", " + firstName);
                           
                }
                else
                {
                    message = i18n("<b>$FULL_NAME</b>: job contract expired <b>$DAY_NUM</b> day(s) ago")
                           .replace("$FULL_NAME", lastName + ", " + firstName)
                           .replace("$DAY_NUM", String.valueOf(-dayNum));
                }

                _messages.addElement(new Message(message, employeeID));
            }
            rs.close();
            conn.close();
        }
        catch (SQLException se)
        { se.printStackTrace(); }
        
        listMessages.setModel(_messages);

    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    // <editor-fold defaultstate="collapsed" desc=" Generated Code ">//GEN-BEGIN:initComponents
    private void initComponents() {
        jScrollPane1 = new javax.swing.JScrollPane();
        bottomPanel = new javax.swing.JPanel();
        jScrollPane2 = new javax.swing.JScrollPane();
        listMessages = new javax.swing.JList();

        setPreferredSize(new java.awt.Dimension(150, 450));
        jScrollPane1.setHorizontalScrollBarPolicy(javax.swing.ScrollPaneConstants.HORIZONTAL_SCROLLBAR_NEVER);
        bottomPanel.setLayout(new javax.swing.BoxLayout(bottomPanel, javax.swing.BoxLayout.Y_AXIS));

        bottomPanel.setMinimumSize(new java.awt.Dimension(150, 10));
        jScrollPane2.setViewportView(listMessages);

        bottomPanel.add(jScrollPane2);

        jScrollPane1.setViewportView(bottomPanel);

        org.jdesktop.layout.GroupLayout layout = new org.jdesktop.layout.GroupLayout(this);
        this.setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
            .add(jScrollPane1, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, 150, Short.MAX_VALUE)
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
            .add(jScrollPane1, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, 450, Short.MAX_VALUE)
        );
    }// </editor-fold>//GEN-END:initComponents
        
    public void tableRowDeleted(Table table, int row) {
        fetchNotifications();
    }

    public void tableRowUpdated(Table table, int row) {
        fetchNotifications();
    }

    public void tableRowInserted(Table table) {
        fetchNotifications();
    }

    public void tableDataChanged(Table table) {
        fetchNotifications();
    }

    public class MessageRenderer extends javax.swing.JTextPane implements ListCellRenderer {
 
        public MessageRenderer() {
          setBorder(new javax.swing.border.EmptyBorder(6, 6, 6, 6));
          setContentType("text/html");
          setFont(new java.awt.Font("Dialog", 0, 12));
        }
    
        public Component getListCellRendererComponent(JList list, Object value, int index, boolean isSelected, boolean cellHasFocus) {
            Message m = (Message) value;
            String text = "<html><body><table width='170'><tr><td>" +
             "<font face=\"sans\" size=\"3\">" + m + "</font></td></tr></table></body></html>";
            setText(text);
      
            if (isSelected) {
              setBackground(new Color(157, 183, 212));
            } else {
            if (index % 2 == 0)
              setBackground(new Color(255, 255, 255));
            else
              setBackground(new Color(238, 246, 255));
            }
      
        return this;
        }
    }
    
    private class Message {
        
        public Message(String message, int employeeID)
        { 
            _message    = message;
            _employeeID = employeeID;
        }
        
        public void setMessage(String value)
        { _message = value; }
        
        public String getMessage()
        { return _message; }
        
        public String toString()
        { return _message; }
        
        public void setEmployeeID(int employeeID)
        { _employeeID = employeeID; }
        
        public int getEmployeeID()
        { return _employeeID; }
        
        private String _message;
        private int _employeeID;
    }

    private DefaultListModel _messages = new DefaultListModel();

    private int _selectedEmployee     = -1;
    private int notificationDateRangeAssistent = 90; // za asistente treba 3 meseca ranije
    private final int maleAgeLimit          = 65;
    private final int femaleAgeLimit        = 65;
    private final int maleWorkYearsLimit    = 40;
    private final int femaleWorkYearsLimit  = 35;
    private final int notificationDateRange = 210; // display message 7 months before POSITION expires
    private final int notificationJobExpireRange = 30; // display message 1 month before JOB expire -
    // misli se kad je neko zaposlen na odredjeno, cesto je to 6 ili 3 meseca.

    
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JPanel bottomPanel;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JScrollPane jScrollPane2;
    private javax.swing.JList listMessages;
    // End of variables declaration//GEN-END:variables

    /**
     * Holds value of property selectedEmployeeID.
     */
    private int selectedEmployeeID;

    /**
     * Utility field used by bound properties.
     */
    private java.beans.PropertyChangeSupport propertyChangeSupport =  new java.beans.PropertyChangeSupport(this);

    /**
     * Adds a PropertyChangeListener to the listener list.
     * @param l The listener to add.
     */
    public void addPropertyChangeListener(java.beans.PropertyChangeListener l) {
        propertyChangeSupport.addPropertyChangeListener(l);
    }

    /**
     * Removes a PropertyChangeListener from the listener list.
     * @param l The listener to remove.
     */
    public void removePropertyChangeListener(java.beans.PropertyChangeListener l) {
        propertyChangeSupport.removePropertyChangeListener(l);
    }

    /**
     * Getter for property selectedEmployeeID.
     * @return Value of property selectedEmployeeID.
     */
    public int getSelectedEmployeeID() {
        return this.selectedEmployeeID;
    }
    
}
